package com.myres.indexitem.db;
import com.myres.db.ConnectionPool;
import com.myres.model.Product;

import java.util.Map;
import java.util.TreeMap;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class IndexItemService {
	public Map<String,List<Product>> getIndexItems(){
		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"select " +
   	    	"  (select res_categories._id from res_categories,res_series where res_categories._id=res_series._cid  and res_series._id=res_products._sid) as c ," +
   	        "  (select res_categories._name from res_categories,res_series where res_categories._id=res_series._cid  and res_series._id=res_products._sid) , " +
   	    	"  _id,_no,_sid,_name,_price,_stock,_icon,_description,_score,_on_index,_on_promotion,_on_recommend , " +
   	    	"  (select res_categories._priority from res_categories,res_series where res_categories._id=res_series._cid  and res_series._id=res_products._sid) as seq " +
   	        "  from res_products where _on_index=1 " +
   	        "  order by seq asc");
   	        rs=pstmt.executeQuery();
   	        return this.populate(rs);
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return null;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
	}
	private Map<String,List<Product>> populate(ResultSet rs) throws SQLException{
		Map<String,List<Product>> m=new TreeMap<String,List<Product>>();
		String cname="";
		int seq=100;
		List<Product> cate=new ArrayList<Product>();
		rs.next();
		int i=0;
		do{     i++;
			    int id=rs.getInt(1);
			    String cname1=rs.getString(2);
			    if(i==1)cname=cname1;
				Product p=new Product();
				p.setId(rs.getInt(3));
				p.setNO(rs.getString(4));
				p.setSid(rs.getInt(5));
				p.setName(rs.getString(6));
				p.setPrice(rs.getFloat(7));
				p.setStock(rs.getInt(8));
				p.setIcon(rs.getString(9));
				p.setDescription(rs.getString(10));
				p.setScore(rs.getInt(11));
				p.setOnIndex(rs.getByte(12));
				p.setOnPromotion(rs.getByte(13));
				p.setOnRecommend(rs.getByte(14));
			    seq=rs.getInt(15);
				
				if(!cname1.equals(cname)){
				    System.out.print("cname="+cname);
					m.put(seq+"|"+cname, cate);
					cate=new ArrayList<Product>();
					cate.add(p);
					cname=cname1;
				}else{
					cate.add(p);
				}
		}while(rs.next());
		m.put(seq+"|"+cname, cate);
		return m;
	}

}
